{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Simulating the data\n",
    "Before we go into some exploratory data analysis, let's see how we simulated the data:\n",
    "\n",
    "```\n",
    "# example of how to run this simulation from jupyter (remove the ! to run from the command line)\n",
    "!python simulate.py -s 0 --stealthy -l logs/jan_2018.csv -hl logs/hackers_jan_2018.csv 31 \"2018-01-01\" 0.01 0.5\n",
    "```\n",
    "\n",
    "| Month | Probability of attack in a given hour | Probability of trying entire userbase | Vary IP addresses? |\n",
    "| --- | --- | --- | --- |\n",
    "| Jan 2018 | 1.00% | 50% | Yes |\n",
    "| Feb 2018 | 0.50% | 25% | Yes |\n",
    "| Mar 2018 | 0.10% | 10% | Yes |\n",
    "| Apr 2018 | 1.00% | 65% | Yes |\n",
    "| May 2018 | 0.01% | 5% | Yes |\n",
    "| Jun 2018 | 0.05% | 5% | Yes |\n",
    "| Jul 2018 | 1.00% | 15% | Yes |\n",
    "| Aug 2018 | 0.50% | 10% | Yes |\n",
    "| Sep 2018 | 0.50% | 10% | No |\n",
    "| Oct 2018 | 0.20% | 12% | No |\n",
    "| Nov 2018 | 0.70% | 17% | Yes |\n",
    "| Dec 2018 | 8.00% | 88% | Yes |\n",
    "| Jan 2019 | 0.80% | 8% | Yes |\n",
    "| Feb 2019 | 0.10% | 18% | Yes |\n",
    "| Mar 2019 | 0.10% | 18% | Yes |\n",
    "\n",
    "We use pandas to combine the files by year. First, we create a utility function for concatenating the files:\n",
    "\n",
    "```\n",
    "import pandas as pd\n",
    "\n",
    "def cat_csvs(format_string_file_pattern, index_col, month_list):\n",
    "    \"\"\"\n",
    "    Utility function for concatentating CSV files from simulation.\n",
    "    \n",
    "    Parameters: \n",
    "        - format_string_file_pattern: The pattern for the file name with `{}` in the place of the month\n",
    "        - index_col: The column with the datetimes to sort on.\n",
    "        - month_list: The list of the months as formatted in the file names.\n",
    "    \n",
    "    Returns:\n",
    "        A concatenated pandas DataFrame\n",
    "    \"\"\"\n",
    "    return pd.concat([\n",
    "        pd.read_csv(\n",
    "            format_string_file_pattern.format(file), index_col=index_col, parse_dates=True\n",
    "        ) for file in month_list\n",
    "    ])\n",
    "```\n",
    "\n",
    "Next, we concatenate the 2018 logs making sure to not record any data from early January 1, 2019 which may have been generated from the Poisson process in December 2018:\n",
    "```\n",
    "logs_2018 = cat_csvs(\n",
    "    'logs/{}_2018.csv', 'datetime', \n",
    "    ['jan', 'feb', 'mar', 'apr', 'may', 'jun', 'jul', 'aug', 'sep', 'oct', 'nov', 'dec']\n",
    ")\n",
    "logs_2018['2018'].sort_index().to_csv('logs/logs_2018.csv') # sometimes the simulation overshoots the end date\n",
    "```\n",
    "\n",
    "Now, we concatenate the 2019 logs remembering to add back the 2019 entries that got into the December 2018 simulation and clip the April 2019 entries from the March simulation:\n",
    "```\n",
    "logs_2019 = pd.concat([cat_csvs('logs/{}_2019.csv', 'datetime', ['jan', 'feb', 'mar']), logs_2018['2019']])\n",
    "logs_2019['2019-Q1'].to_csv('logs/logs_2019.csv') # sometimes the simulation overshoots the end date\n",
    "```\n",
    "\n",
    "After we have the login attempts logs, we concatenate the 2018 hacker logs:\n",
    "```\n",
    "hackers_2018 = cat_csvs(\n",
    "    'logs/hackers_{}_2018.csv', 'start', \n",
    "    ['jan', 'feb', 'mar', 'apr', 'may', 'jun', 'jul', 'aug', 'sep', 'oct', 'nov', 'dec']\n",
    ")\n",
    "hackers_2018['2018'].sort_index().to_csv('logs/hackers_2018.csv')\n",
    "```\n",
    "\n",
    "Concatenating the 2019 hacker logs is the same process:\n",
    "```\n",
    "hackers_2019 = pd.concat([\n",
    "    cat_csvs('logs/hackers_{}_2019.csv', 'start', ['jan', 'feb', 'mar']), hackers_2018['2019']\n",
    "])\n",
    "hackers_2019['2019-Q1'].sort_index().to_csv('logs/hackers_2019.csv')\n",
    "```\n",
    "\n",
    "The process of building the CSV files from the individual simulations is contained in `merge_logs.py` and the entire process is in the bash script `run_simulations.sh`. You don't have to run either of these."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create SQLite Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "# read in files\n",
    "logs_2018 = pd.read_csv('logs/logs_2018.csv', index_col='datetime')\n",
    "logs_2019 = pd.read_csv('logs/logs_2019.csv', index_col='datetime')\n",
    "hackers_2018 = pd.read_csv('logs/hackers_2018.csv', index_col='start')\n",
    "hackers_2019 = pd.read_csv('logs/hackers_2019.csv', index_col='start')\n",
    "\n",
    "# write to database\n",
    "with sqlite3.connect('logs/logs.db') as conn:\n",
    "    logs_2018.to_sql('logs', conn, if_exists='replace')\n",
    "    logs_2019.to_sql('logs', conn, if_exists='append')\n",
    "    hackers_2018.to_sql('attacks', conn, if_exists='replace')\n",
    "    hackers_2019.to_sql('attacks', conn, if_exists='append')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
